using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using System.Threading;
using Common.Utilities;
using Common.Utilities.Dispatcher;

namespace Workflows.Components.DB.Sql
{
    /// <summary></summary>
    public class SqlWorker:IWorker
    {
        private Thread _WorkerThread;  
        private SqlInstruction _Instruction;
        private SqlOutput _Output;
        private Guid _TicketID;
        private RunContext _Context;
        private string _WorkflowName;
        private string _ComponentName;

        /// <summary>
        /// 
        /// </summary>
        public string ComponentName
        {
            get { return _ComponentName; }
        }

        /// <summary>
        /// 
        /// </summary>
        public string WorkflowName
        {
            get { return _WorkflowName; }
        }

        /// <summary></summary>
        public Thread WorkerThread
        {
            get { return _WorkerThread; }
        }
        /// <summary></summary>
        public InstructionBase Instruction
        {
            get { return _Instruction; }
        }
        /// <summary></summary>
        public ResultBase Output
        {
            get { return _Output; }
        }
        /// <summary></summary>
        public Type ComponentType
        {
            get { return typeof(SqlComponent); }
        }
        /// <summary></summary>
        public RunContext Context
        {
            get { return _Context; }
            set { _Context = value; }
        }
        /// <summary></summary>
        public void SetInstruction(Guid ticketGuid, InstructionBase request, string workflowName, string componentName)
        {
            this._TicketID = ticketGuid;
            this._Instruction = (SqlInstruction) request;
            this._WorkflowName = workflowName;
            this._ComponentName = componentName;
        }
        /// <summary></summary>
        public void SetInstruction(RunContext context, Guid ticketGuid, InstructionBase request, string workflowName, string componentName)
        {
            this._Context = context;
            this._TicketID = ticketGuid;
            this._Instruction = (SqlInstruction) request;
            this._WorkflowName = workflowName;
            this._ComponentName = componentName;
        }
        /// <summary></summary>
        public void DoWork()
        {
            if(this.WorkerStartedEvent !=null)
            {
                this.WorkerStartedEvent(this._TicketID, this, this.GetType(), this._Instruction);
            }
            this._WorkerThread = Thread.CurrentThread;
            this.RunSqlTask();
        }
        /// <summary></summary>
        public void DoWorkAsync()
        {
            if (this.WorkerStartedEvent != null)
            {
                this.WorkerStartedEvent(this._TicketID, this, this.GetType(), this._Instruction);
            }
            this._WorkerThread = new Thread(new ThreadStart(this.RunSqlTask));
            this._WorkerThread.Start();
        }
        /// <summary></summary>
        public void CancelWork()
        {
            if(this._WorkerThread !=null && this._WorkerThread.IsAlive)
            {
                this._WorkerThread.Abort();
                this._WorkerThread.Join();

                if(this.WorkerCancelledEvent !=null)
                {
                    this.WorkerCancelledEvent(this._TicketID, this.GetType(), this._Instruction);
                }
            }
        }

        public event WorkerStarted WorkerStartedEvent;
        public event WorkerFinished WorkerFinishedEvent;
        public event WorkerHasError WorkerErredEvent;
        public event WorkerCancelled WorkerCancelledEvent;
        public event WorkerStatusChanged WorkerStatusChangedEvent;

        #region work
        private void RunSqlTask()
        {
            try
            {
                List<SimpleSqlParameter> parameters = new List<SimpleSqlParameter>();
                if(this._Instruction.Parameters !=null && this._Instruction.Parameters.Length>0)
                    parameters.AddRange(this._Instruction.Parameters);

                SqlOutputType outputType = SqlOutputType.Nothing;
                if(this._Instruction.SqlCommandType==CommandType.StoredProcedure)
                {
                    outputType = SqlOutputType.OutputParameters;
                }
                else
                {
                    switch(this._Instruction.QueryOutputType)
                    {
                        case SqlQueryOutputType.Nothing:
                            outputType = SqlOutputType.Nothing;
                            break;
                        case SqlQueryOutputType.Scalar:
                            outputType = SqlOutputType.Scalar;
                            break;
                        case SqlQueryOutputType.Table:
                            outputType = SqlOutputType.Table;
                            break;
                    }
                }

                object returnValue =
                    this.ExecuteSql(this._Instruction.ConnStr, this._Instruction.SqlCommandText,
                                    outputType, ref parameters, this._Instruction.QueryScalarOutputType,
                                    this._Instruction.QueryOutputTableDefinition);


                SqlOutputType sqlOutputType = SqlOutputType.Nothing;
                object scalarObj = null;
                DataTable outputTable = null;
                if (this._Instruction.SqlCommandType == CommandType.StoredProcedure)
                {
                    sqlOutputType = SqlOutputType.OutputParameters;
                }
                else
                {
                    if (this._Instruction.QueryOutputType == SqlQueryOutputType.Scalar)
                    {
                        sqlOutputType = SqlOutputType.Scalar;
                        scalarObj = returnValue;
                    }
                    else if (this._Instruction.QueryOutputType == SqlQueryOutputType.Table)
                    {
                        sqlOutputType = SqlOutputType.Table;
                        if (returnValue != null && returnValue.GetType() == typeof(DataTable))
                            outputTable = (DataTable) returnValue;
                    }
                }




                List<SimpleSqlParameter> returnParameters = new List<SimpleSqlParameter>();
                if (parameters.Count > 0)
                {
                    foreach (SimpleSqlParameter sqlParam in parameters)
                    {
                        if (sqlParam.Direction != ParameterDirection.Input)
                        {
                            returnParameters.Add(sqlParam);
                        }
                    }
                }
                this._Output =
                    new SqlOutput(this._Instruction.OwnerComponentID, this._TicketID, sqlOutputType,
                                  returnParameters.ToArray(), scalarObj, outputTable);

                    
                if(this.WorkerFinishedEvent !=null)
                {
                    this.WorkerFinishedEvent(this._TicketID, this.GetType(), this._Instruction, this._Output);
                }
            }
            catch(Exception ex)
            {
                if(this.WorkerErredEvent !=null)
                {
                    this.WorkerErredEvent(this._TicketID, this.GetType(), this._Instruction, ex.ToString(),true);
                }
            }
        }
        /// <summary></summary>
        public object ExecuteSql(string connStr, string sql, SqlOutputType outputType, 
            ref List<SimpleSqlParameter> parameters, 
            SimpleDataType scalarOutputType, DataTable tblDefinition)
        {
            object output = null;
            SqlConnection conn=new SqlConnection(connStr);
            try
            {
                conn.Open();
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandTimeout = 0; // no timeout
                cmd.CommandText = sql;
                cmd.Parameters.Clear();
                Dictionary<string, SqlParameter> parameterCollection=new Dictionary<string,SqlParameter>();
                if (parameters != null && parameters.Count > 0)
                {
                    foreach (SimpleSqlParameter simpleParam in parameters)
                    {
                        SqlDbType sqlDbType =
                            FieldDataType.ToDbType(
                                SqlDbTypeUtil.ToSystemType(simpleParam.DbType).ToString());
                        SqlParameter sqlParam =
                            new SqlParameter(
                                simpleParam.ParameterName,
                                sqlDbType,
                                FieldDataType.GetFieldDefaultSize(sqlDbType));
                        sqlParam.Direction = simpleParam.Direction;
                        if (simpleParam.Value != null)
                            sqlParam.Value = simpleParam.Value;
                        else
                            sqlParam.Value = DBNull.Value;
                        parameterCollection.Add(simpleParam.ParameterName, sqlParam);
                        cmd.Parameters.Add(sqlParam);
                    }
                }

                switch(outputType)
                {
                    case SqlOutputType.OutputParameters:
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.ExecuteNonQuery();

                        Dictionary<string, object> outputParamValues = new Dictionary<string, object>();
                        if (parameters != null && parameters.Count > 0)
                        {
                            foreach (SimpleSqlParameter simpleParam in parameters)
                            {
                                if (simpleParam.Direction == ParameterDirection.Output)
                                {
                                    object paramValue = parameterCollection[simpleParam.ParameterName].Value;
                                    outputParamValues.Add(simpleParam.ParameterName, paramValue);
                                    // output = paramValue;
                                    simpleParam.Value = paramValue;
                                }
                            }
                        }
                        //if (outputParamValues.Count > 1)
                        //{
                        //    output = outputParamValues;
                        //}
                        //else if (outputParamValues.Count == 0)
                        //{
                        //    output = null;
                        //}

                        break;
                    case SqlOutputType.Nothing:
                        cmd.CommandType = CommandType.Text;
                        cmd.ExecuteNonQuery();
                        break;
                    case SqlOutputType.Scalar:
                        cmd.CommandType = CommandType.Text;
                        output = cmd.ExecuteScalar();
                        break;
                    case SqlOutputType.Table:
                        cmd.CommandType = CommandType.Text;
                        DataTable tbl = tblDefinition.Clone();
                        SqlDataReader reader = cmd.ExecuteReader();
                        if (reader != null)
                        {
                            while (reader.Read())
                            {
                                DataRow dr = tbl.NewRow();
                                for (int i = 0; i < tbl.Columns.Count; i++)
                                {
                                    if (reader[tbl.Columns[i].ColumnName] != null)
                                    {
                                        dr[tbl.Columns[i].ColumnName] = reader[tbl.Columns[i].ColumnName];
                                    }
                                }
                                tbl.Rows.Add(dr);
                            }
                            reader.Close();
                        }

                        output = tbl;
                        break;
                }
            }
            catch(Exception ex)
            {
                throw new Exception("Error while executing sql command: " + ex.ToString());
            }
            finally
            {
                conn.Close();
            }
            return output;
        }
        #endregion
    }
}
